Customer Shopping Trend Analysis

library(tidyverse)
library(knitr)
library(gt)
library(readxl)
library(readr)
library(ggplot2)
library(dataMaid)
library(RSQLite)
library(tidytext) 
library(dplyr)
library(gridExtra)
library(lubridate)
library(janitor)
library(ggthemes)

shopping_trends_2 <- read_csv("shopping_trends_updated.csv")

## Clean the col names
shopping_trends_1 <-shopping_trends_1 %>%
  clean_names()%>%
  setNames(tools::toTitleCase(names(.)))

shopping_trends_2 <-shopping_trends_2 %>%
  clean_names()%>%
  setNames(tools::toTitleCase(names(.)))

## Check the results
shopping_trends_1 %>%
  head(1)

shopping_trends_2%>%
  head(1)

## Use SQLite function to join two tables
con <- dbConnect(RSQLite::SQLite(), dbname = "your_database.db")
dbWriteTable(con, "shopping_trends_2_table", shopping_trends_2,overwrite = TRUE)
dbWriteTable(con, "shopping_trends_1_table", shopping_trends_1,overwrite = TRUE)

query <- "
SELECT 
  shopping_trends_2_table.*,
  shopping_trends_1_table.Preferred_payment_method
FROM 
  shopping_trends_2_table
LEFT JOIN 
  shopping_trends_1_table 
ON 
  shopping_trends_2_table.Customer_id = shopping_trends_1_table.Customer_id
"
result1 <- dbGetQuery(con, query)

apply(result1, 2, max, na.rm = TRUE)

shopping_trends <- as.data.frame(result1)

summary(shopping_trends)

Age_group <- function(age) {
  if (age >= 18 & age <= 35) {
    return("Youth")
  } else if (age > 35 & age <= 60) {
    return("Middle_age")
  } else {
    return("Elderly")
  }
}


df <- data.frame(Age = shopping_trends$Age, Gender = shopping_trends$Gender)
df$Age_group <- sapply(df$Age, Age_group)
summary_df <- df %>%
  dplyr::group_by(Age_group, Gender) %>%
  dplyr::summarize(count = n(), .groups = "drop") %>%
  dplyr::group_by(Age_group) %>%
  dplyr::mutate(total_count = sum(count),
         percentage = paste0(round(count / total_count * 100, 2), "%")) %>%
  dplyr::ungroup() %>%
  dplyr::arrange(Age_group, desc(Gender)) %>%
  dplyr::select(-total_count)

summary_df %>%
  gt()

# 2.1The most popular category
 Category_group <- shopping_trends %>%
  group_by(Category) %>%
  summarise(count = n()) %>%
  arrange(desc(count))
pie(Category_group$count, 
    labels = Category_group$Category, main = "Pie Chart")

## The most popular category is Clothing

# 2.2 The three largest items proportion in Clothing
 items_category <- dplyr:::select(shopping_trends,Item_purchased, Category)
 items_category_1 <-items_category %>%
   dplyr:::filter(Category == "Clothing")%>%
   group_by(Item_purchased)%>%
   summarise(count = n())%>%
   slice_max(order_by=Item_purchased,
            n=3)
items_category_1 %>%
  gt()

shopping_trends$Season <- factor(shopping_trends$Season, levels = c("Spring", "Summer", "Fall", "Winter"))
ggplot(shopping_trends) +
 aes(x = Season, y = Purchase_amount_usd) +
 geom_boxplot(fill = "#112446") +
 labs(x = "Season", y = "Purchase amount USD", title = "Season vs. Purchase Amount ") +
theme(axis.text.x = element_text(size = 15))+
 theme_minimal()

## The season of Fall is the highest in Purchase amount 

common_type <- shopping_trends %>%
  filter(Review_rating > 4) %>%
  group_by(Shipping_type) %>%
  summarise(n = n()) %>%
  arrange(desc(n)) %>%
  slice(1)
common_type %>%
  gt()

season_color_sales <- shopping_trends %>%
  group_by(Season, Color) %>%
  summarise(Count = n()) 
top_colors <- season_color_sales %>%
  group_by(Season) %>%
  top_n(1, Count)
top_colors <- top_colors[match(c("Spring", "Summer", "Fall", "Winter"),
                               top_colors$Season), ]
top_colors %>%
  ungroup() %>%
  gt()

avg_purchase <- mean(
  shopping_trends$Purchase_amount_usd[
    shopping_trends$Previous_purchases > 30
    ],
  na.rm = TRUE
  ) %>%
  round(digits = 2)
print(avg_purchase)

# Filter for customers with a subscription and who used Venmo
filtered_data <- shopping_trends %>%
  filter(Subscription_status == "Yes", Payment_method == "Venmo")

# Calculate the average purchase amount
average_purchase_amount <- filtered_data %>%
  summarise(AverageAmount = mean(Purchase_amount_usd))

#print(average_purchase_amount)

grid.arrange(top_purchases_plot, top_members_plot, ncol = 2)